Some database systems store transactional or time cyclic data. To improve transactional throughput, the data can be distributed in different partitions to balance the workload. For time cyclic data management, data for a defined period of time (such as each hour, day, week, month, quarter, year, etc.) is kept in distinct partitions to improve the manageability of data. One common operation on the database table is to load new sets of data. Three ways of loading the new sets of data include: simple INSERTs into the table; loading the data via utilities; and attaching a table with data for a new time period to an existing table within the database.
Simple INSERTs insert the new data into the database table row by row and updates the database table indices simultaneously. During the INSERTs, the rest of the database table is available for queries, subject to isolation level requirements. However, this approach is slow and resource intensive.
Loading the new data using utilities can be faster than using simple INSERTs, however, to raise performance, the database table is locked, which can make the application accessing this table, even if infrequently, unavailable.
The new data may also be prepared in a separate table, and this separate table can then be attached to the database table through an ATTACH operation. The ATTACH operation is quicker than the simple INSERTs and the loading using utilities. However, since the ATTACH operation modifies the physical structure of the table, an exclusive lock to the database table is taken. In taking an exclusive lock on the database table, every query on the database table will have to wait or will timeout. Furthermore, the ATTACH operation itself cannot proceed until it either drains or preempts all existing access to the table. If draining is the only option and the existing access involves a long-running report, the ATTACH operation is unable to proceed until the report completes or is terminated. For database systems in a 24/7 environment, delay or downtime to perform the ATTACH operation may be undesirable.